package com.huabo.contract.mysql.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.mysql.entity.TblContractCollectionMySql;

import java.math.BigDecimal;

public class TblContractCollectionMySqlMapperSqlConfig {
    public String selectListByPageInfo(PageInfo<TblContractCollectionMySql> pageInfo, String staffid, String contractname, String contractno, BigDecimal pid, TblContractCollectionMySql collection) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM" +
                "(SELECT * FROM TBL_CONTRACT_COLLECTION TCC \n" +
                "LEFT JOIN TBL_CONTRACT_INVOICESMANAGEMEN TCI ON TCC.INVOICEID = TCI.INVOICEID\n" +
                "LEFT JOIN TBL_CYHW_UNIT TCU ON TCC.CONTRACTID = TCU.CONTRACTID \n" +
                "LEFT JOIN TBL_CYHW_PROJECTBUDGET TCP ON TCU.CONTRACTXDFXINFO = TCP.BUDGETID " +

                "LEFT JOIN TBL_CONTRACT_PLANNODE TCP ON TCC.NODEID = TCP.NODEID\n" +
                "LEFT JOIN TBL_COUNTERPART_BANKINFO TCB ON TCC.COUNTERBANK = TCB.BANKID\n" +
                "LEFT JOIN TBL_ORG_BANKACCOUNT TOB ON TCC.ORGBANK = TOB.BANKID " +
                "WHERE LINKORG = "+ pid);

        if (collection.getCreatestaff() != null) {
            sbSql.append(" AND TCC.CREATESTAFF = " + collection.getCreatestaff());
        }
        if (collection.getCollectionorgname() != null && !"".equals(collection.getCollectionorgname())) {
            sbSql.append(" AND TCC.COLLECTIONORGNAME LIKE '%" + collection.getCollectionorgname() + "%'");
        }
        if (collection.getContract() != null) {
            if (contractname != null && !"".equals(contractname)) {
                sbSql.append(" AND TCU.CONTRACTNAME LIKE '%" + contractname+ "%'");
            }

            if (contractno != null && !"".equals(contractno)) {
                sbSql.append( " AND TCU.CONTRACTNO LIKE '%" +contractno+ "%'");
            }
        }
        sbSql.append(" ORDER BY COLLECTIONID DESC) BUDGET WHERE ROWNUM <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        return sbSql.toString();
    }

    public String selectCountByPageInfo( String staffid, String contractname, String contractno,BigDecimal pid, TblContractCollectionMySql collection) {
    	TblContractCollectionMySql contract = new TblContractCollectionMySql();

        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CONTRACT_COLLECTION TCC LEFT JOIN TBL_CONTRACT_INVOICESMANAGEMEN TCI ON TCC.INVOICEID = TCI.INVOICEID " +
                "LEFT JOIN TBL_CYHW_UNIT TCU ON TCC.CONTRACTID = TCU.CONTRACTID WHERE LINKORG = "+ pid );

        if (collection.getCreatestaff() != null) {
            sbSql.append(" AND TCC.CREATESTAFF = " + collection.getCreatestaff());
        }
        if (collection.getCollectionorgname() != null && !"".equals(collection.getCollectionorgname())) {
            sbSql.append(" AND TCC.COLLECTIONORGNAME LIKE '%" + collection.getCollectionorgname() + "%'");
        }
        if (contract.getContract() != null) {
            if (contractname != null && !"".equals(contractname)) {
                sbSql.append(" AND TCU.CONTRACTNAME LIKE '%" + contractname+ "%'");
            }

            if (contractno != null && !"".equals(contractno)) {
                sbSql.append( " AND TCU.CONTRACTNO LIKE '%" +contractno+ "%'");
            }
        }
        return sbSql.toString();
    }


    public String UpdateOldCollection(TblContractCollectionMySql oldCollection) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_CONTRACT_COLLECTION SET INVOICEID = '"+oldCollection.getInvoiceid()+"'");

//        if(oldCollection.getNodemoney() != null && !"".equals(oldCollection.getNodemoney())) {
//            sql.append(" , NODEMONEY = '"+oldCollection.getNodemoney()+"'");
//        }
        if(oldCollection.getCollectionbank() != null && !"".equals(oldCollection.getCollectionbank())) {
            sql.append(" , COLLECTIONBANK = '"+oldCollection.getCollectionbank()+"'");
        }
        if(oldCollection.getCollectionskdate() != null && !"".equals(oldCollection.getCollectionskdate())) {
            sql.append(" ,COLLECTIONSKDATE = TO_DATE('"+DateUtil.parseDate(oldCollection.getCollectionskdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldCollection.getCollectionaccount() != null && !"".equals(oldCollection.getCollectionaccount())) {
            sql.append(" , COLLECTIONACCOUNT = '"+oldCollection.getCollectionaccount()+"'");
        }
        if(oldCollection.getCollectionorgname() != null && !"".equals(oldCollection.getCollectionorgname())) {
            sql.append(" , COLLECTIONORGNAME = '"+oldCollection.getCollectionorgname()+"'");
        }
        if(oldCollection.getContractid() != null && !"".equals(oldCollection.getContractid())) {
            sql.append(" , CONTRACTID = '"+oldCollection.getContractid()+"'");
        }
        if(oldCollection.getNodeid() != null && !"".equals(oldCollection.getNodeid())) {
            sql.append(" , NODEID = '"+oldCollection.getNodeid()+"'");
        }
        if(oldCollection.getCreatestaff() != null && !"".equals(oldCollection.getCreatestaff())) {
            sql.append(" , CREATESTAFF = '"+oldCollection.getCreatestaff()+"'");
        }
        if(oldCollection.getCollectionstatus() != null && !"".equals(oldCollection.getCollectionstatus())) {
            sql.append(" , COLLECTIONSTATUS = '"+oldCollection.getCollectionstatus()+"'");
        }
        if(oldCollection.getLinkorg() != null && !"".equals(oldCollection.getLinkorg())) {
            sql.append(" , LINKORG = '"+oldCollection.getLinkorg()+"'");
        }
        if(oldCollection.getCreatedate() != null && !"".equals(oldCollection.getCreatedate())) {
            sql.append(" ,CREATEDATE = TO_DATE('"+DateUtil.parseDate(oldCollection.getCreatedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
//        if(oldCollection.getNodemoney() != null && !"".equals(oldCollection.getNodemoney())) {
//            sql.append(" , NODEMONEY = '"+ oldCollection.getNodemoney()+"'");
//        }
        if(oldCollection.getCounterbank() != null && !"".equals(oldCollection.getCounterbank())) {
            sql.append(" , COUNTERBANK = '"+oldCollection.getCounterbank()+"'");
        }
        if(oldCollection.getOrgbank() != null && !"".equals(oldCollection.getOrgbank())) {
            sql.append(" , ORGBANK = '"+ oldCollection.getOrgbank()+"'");
        }
        sql.append(" WHERE COLLECTIONID = '"+oldCollection.getCollectionid()+"'");
        return sql.toString();
    }


    public String SaveMengerCollectionEntity(TblContractCollectionMySql collection) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_CONTRACT_COLLECTION (");
        StringBuffer value = new StringBuffer(" VALUES (");

        if(collection.getInvoiceid() != null) {
            column.append("INVOICEID");
            value.append(",'"+collection.getInvoiceid()+"'");
        }
        if(collection.getCollectionbank() != null) {
            column.append(",COLLECTIONBANK");
            value.append(",'"+collection.getCollectionbank()+"'");
        }
        if(collection.getCollectionskdate() != null) {
            column.append(",COLLECTIONSKDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(collection.getCollectionskdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(collection.getCollectionaccount() != null) {
            column.append(",COLLECTIONACCOUNT");
            value.append(",'"+collection.getCollectionaccount()+"'");
        }
        if(collection.getCollectionorgname() != null) {
            column.append(",COLLECTIONORGNAME");
            value.append(",'"+collection.getCollectionorgname()+"'");
        }
        if(collection.getContractid() != null) {
            column.append(",CONTRACTID");
            value.append(",'"+collection.getContractid()+"'");
        }
        if(collection.getNodeid() != null) {
            column.append(",NODEID");
            value.append(",'"+collection.getNodeid()+"'");
        }
        if(collection.getCreatestaff() != null) {
            column.append(",CREATESTAFF");
            value.append(",'"+collection.getCreatestaff()+"'");
        }
        if(collection.getCollectionstatus() != null) {
            column.append(",COLLECTIONSTATUS");
            value.append(",'"+collection.getCollectionstatus()+"'");
        }
        if(collection.getLinkorg() != null) {
            column.append(",LINKORG");
            value.append(",'"+collection.getLinkorg()+"'");
        }
        if(collection.getCreatedate() != null ) {
            column.append(",CREATEDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(collection.getCreatedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
//        if(collection.getLinkorg() != null) {
//            column.append(",LINKORG");
//            value.append(",'"+collection.getLinkorg()+"'");
//        }
//        if(collection.getNodemoney() != null) {
//            column.append(",NODEMONEY");
//            value.append(",'"+collection.getNodemoney()+"'");
//        }
        if(collection.getCounterbank() != null) {
            column.append(",COUNTERBANK");
            value.append(",'"+collection.getCounterbank()+"'");
        }
        if(collection.getOrgbank() != null) {
            column.append(",ORGBANK");
            value.append(",'"+collection.getOrgbank()+"'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }
}
